package cadc.daos;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.windpicker.common.utils.DateUtil;
import com.windpicker.common.utils.Encoder;

import cadc.models.Account;
import cadc.models.Entrant;
import cadc.models.FileItem;
import cadc.models.Group;
import cadc.models.Ping;

public class GroupDao extends JdbcDaoSupport {
	public int addGroup(Account account){
		String sql = "insert into groups (id, name, password, registrationTime) values (null, ?, ?, ?)";
		if(super.getJdbcTemplate().update(sql, account.getName(), Encoder.encryptMD5(account.getPassword()), DateUtil.dateToString(new Date()))>0){
			return super.getJdbcTemplate().queryForInt("select max(id) from accounts");
		}else return 0;
	}
	
	public Group getBasicInfo(int id) {
		String sql = "select * from groups where id="+id;
		List<Group> items = super.getJdbcTemplate().query(sql, new GroupRowMapper());
		if(items.isEmpty()||items.size()==0)return null;
		return items.get(0);
	}
	
	public boolean updateBasicInfo(Group cadcUser) {
		String sql="update groups set workName=?, workType=?, society=?, remark=?, status=?,firstMem=?, secondMem=?, thirdMem=?, teacher=? where groups.id=?";
		if(super.getJdbcTemplate().update(sql, cadcUser.getWorkName(), cadcUser.getWorkType(), cadcUser.getSociety(), cadcUser.getRemark(), cadcUser.getStatus(), cadcUser.getFirstMem().getId(), cadcUser.getSecondMem().getId(), cadcUser.getThirdMem().getId(), cadcUser.getTeacher().getId(), cadcUser.getId())>0){
			return true;
		}else return false;
	}
	
	public boolean updateWork(int work, int groupId) {
		String sql="update groups set status=3, work="+work+" where id="+groupId;
		if(super.getJdbcTemplate().update(sql)>0){
			return true;
		}else return false;
	}
	
	public List<Group> getBasicInfoList() {
		String sql = "select * from groups where status>1 order by work_type";
		return super.getJdbcTemplate().query(sql, new GroupRowMapper());
	}
	
	public List<Group> listGroupWithFirstMemByWorkType(int workType, int basicStatus){
		String sql = "select * from groups, entrants where groups.status>"+(basicStatus-1)+" and groups.workType="+workType+" and groups.firstMem=entrants.id order by groups.name";
		return super.getJdbcTemplate().query(sql, new GroupWithFirstMemRowMapper());
	}

	public List<Group> getBasicInfoList(int type) {
		String sql = "select * from groups where status=3 and type="+type;
		return super.getJdbcTemplate().query(sql, new GroupRowMapper());
	}
	
	public List<Group> listBasicInfoByWorkType(int workType, int type) {
		String sql = "select * from groups where status=3 and workType="+workType+" and type="+type+" order by name";
		return super.getJdbcTemplate().query(sql, new GroupRowMapper());
	}
	
	public List<Group> getBasicInfoListByStatus(int status) {
		String sql = "select * from groups where status="+status+" order by name";
		return super.getJdbcTemplate().query(sql, new GroupRowMapper());
	}
	
	public List<Group> listBasic(){
		String sql  = "select id, name from groups where status=3 order by name";
		return super.getJdbcTemplate().query(sql, new GRowMapper());
	}
	
	public List<Group> listWorks(int workType, long userId){
		String sql = "select * from groups, files, pings where groups.status=3 and groups.workType="+workType+" and groups.work=files.id and groups.id=pings.groupId and pings.pingId="+userId;
		return super.getJdbcTemplate().query(sql, new WorkRowMapper());
	}
	
	public void changeName(String newName, int groupId){
		String sql = "update groups set name=? where id="+groupId;
		super.getJdbcTemplate().update(sql, newName);
	}
	
	class GRowMapper implements RowMapper<Group>{

		@Override
		public Group mapRow(ResultSet rs, int arg1) throws SQLException {
			Group g = new Group();
			g.setId(rs.getInt("id"));
			Account a = new Account();
			a.setId(rs.getInt("groups.id"));
			a.setName(rs.getString("groups.name"));
			g.setAccount(a);
			return g;
		}
		
	}
	
	class GroupRowMapper implements RowMapper<Group>{
		@Override
		public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
			Group user = new Group();
			user.setId(rs.getInt("id"));
			Entrant en1 = new Entrant();
			en1.setId(rs.getInt("firstMem"));
			user.setFirstMem(en1);
			Entrant en2 = new Entrant();
			en2.setId(rs.getInt("secondMem"));
			user.setSecondMem(en2);
			Entrant en3 = new Entrant();
			en3.setId(rs.getInt("thirdMem"));
			user.setThirdMem(en3);
			Entrant en4 = new Entrant();
			en4.setId(rs.getInt("teacher"));
			user.setTeacher(en4);
			FileItem fi = new FileItem();
			fi.setId(rs.getInt("work"));
			user.setWork(fi);
			user.setRemark(rs.getString("remark"));
			user.setSociety(rs.getString("society"));
			user.setWorkName(rs.getString("workName"));
			user.setWorkType(rs.getInt("workType"));
			user.setStatus(rs.getInt("status"));
			return user;
		}	
	}
	class WorkRowMapper implements RowMapper<Group>{

		@Override
		public Group mapRow(ResultSet rs, int arg1) throws SQLException {
			Group user = new Group();
			user.setId(rs.getInt("groups.id"));
			Account a = new Account();
			a.setId(rs.getInt("groups.id"));
			a.setName(rs.getString("groups.name"));
			user.setAccount(a);
			FileItem fi = new FileItem();
			fi.setId(rs.getInt("groups.work"));
			fi.setItemFileName(rs.getString("files.name"));
			fi.setSize(rs.getInt("files.size"));
			user.setWork(fi);
			Ping ping = new Ping();
			int[] pings = new int[6];
			int fen=0;
			for(int i=0; i<6; i++){
				pings[i] = rs.getInt("pings.mark"+i);
				fen+=pings[i];
			}
			ping.setPings(pings);
			ping.setFen(fen);
			ping.setPingId(rs.getInt("pings.pingId"));
			ping.setNum(rs.getInt("pings.num"));
			ping.setPingyu(rs.getString("pings.pingyu"));
			ping.setAddable(rs.getBoolean("pings.addable"));
			ping.setPassed(rs.getBoolean("pings.passed"));
			user.setPing(ping);
			user.setRemark(rs.getString("groups.remark"));
			user.setWorkName(rs.getString("groups.workName"));
			user.setWorkType(rs.getInt("groups.workType"));
			return user;
		}
		
	}
	class GroupWithFirstMemRowMapper implements RowMapper<Group>{
		@Override
		public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
			Group user = new Group();
			user.setId(rs.getInt("groups.id"));
			Account a = new Account();
			a.setId(rs.getInt("groups.id"));
			a.setName(rs.getString("groups.name"));
			user.setAccount(a);
			Entrant en1 = new Entrant();
			en1.setId(rs.getInt("groups.firstMem"));
			en1.setName(rs.getString("entrants.name"));
			en1.setCollege(rs.getString("entrants.college"));
			en1.setGender(rs.getString("entrants.gender"));
			en1.setNumber(rs.getString("entrants.number"));
			en1.setPhone(rs.getString("entrants.phone"));
			en1.setEmail(rs.getString("entrants.email"));
			user.setFirstMem(en1);
			Entrant en2 = new Entrant();
			en2.setId(rs.getInt("groups.secondMem"));
			user.setSecondMem(en2);
			Entrant en3 = new Entrant();
			en3.setId(rs.getInt("groups.thirdMem"));
			user.setThirdMem(en3);
			Entrant en4 = new Entrant();
			en4.setId(rs.getInt("groups.teacher"));
			user.setTeacher(en4);
			FileItem fi = new FileItem();
			fi.setId(rs.getInt("groups.work"));
			user.setWork(fi);
			user.setRemark(rs.getString("groups.remark"));
			user.setSociety(rs.getString("groups.society"));
			user.setWorkName(rs.getString("groups.workName"));
			user.setWorkType(rs.getInt("groups.workType"));
			user.setStatus(rs.getInt("groups.status"));
			return user;
		}	
	}
}
